package com.micro.glszh.stat.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.micro.glszh.stat.dao.BizBaseRecordMapper;
import com.micro.glszh.stat.domain.BizBaseRecord;
import com.micro.glszh.stat.domain.BizDiseaseDetail;
import com.micro.glszh.stat.domain.BizMatterDetail;
import com.micro.glszh.stat.domain.dto.RecordDto;
import com.micro.glszh.stat.exception.BadRequestException;
import com.micro.glszh.stat.service.BizBaseRecordService;
import com.micro.glszh.stat.service.BizDiseaseDetailService;
import com.micro.glszh.stat.service.BizMatterDetailService;
import com.micro.glszh.stat.utils.LocalUserUtils;
import com.micro.glszh.stat.utils.PageResult;
import com.micro.glszh.stat.utils.PageUtil;
import com.micro.glszh.stat.utils.PoiExcelUtil;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * BizBaseRecordServiceImpl
 *
 * @Author gaopengfei
 * @create 2024/10/21 15:53
 */
@RequiredArgsConstructor
@Service
public class BizBaseRecordServiceImpl extends ServiceImpl<BizBaseRecordMapper, BizBaseRecord> implements BizBaseRecordService {

    private final BizDiseaseDetailService bizDiseaseDetailService;

    private final BizMatterDetailService bizMatterDetailService;

    private final BizBaseRecordMapper bizBaseRecordMapper;

    private static final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");


    @Transactional(rollbackFor = BadRequestException.class)
    @Override
    public Boolean insertOrUpdateRecord(RecordDto record) {
        boolean result = false;
        BizBaseRecord bizBaseRecord = record.getBizBaseRecord();
        if (Objects.isNull(bizBaseRecord.getId())) {
            BizBaseRecord one = this.getOne(new LambdaQueryWrapper<BizBaseRecord>().eq(BizBaseRecord::getCreateTime, LocalDate.now()).eq(BizBaseRecord::getCreateBy, LocalUserUtils.getUser().getName()));
            if (Objects.nonNull(one)) {
                throw new BadRequestException("今日已填，如需修改，请找对对应今天的记录进行更改");
            }
        }
        bizBaseRecord.setGrade(LocalUserUtils.getUser().getGrade());
        bizBaseRecord.setClassId(LocalUserUtils.getUser().getClassId());
        bizBaseRecord.setDiseaseNum(record.getBizDiseaseDetailList().size());
        bizBaseRecord.setMatterNum(record.getBizMatterDetailList().size());
        List<BizMatterDetail> bizMatterDetailList = record.getBizMatterDetailList();
        List<BizDiseaseDetail> bizDiseaseDetailList = record.getBizDiseaseDetailList();
        result = this.saveOrUpdate(bizBaseRecord);
        Set<Long> addMatter = new HashSet<>();
        Set<Long> addDisease = new HashSet<>();
        bizMatterDetailList.stream().forEach(item -> {
            item.setRecordId(bizBaseRecord.getId());
            item.setClassId(bizBaseRecord.getClassId());
            item.setGrade(bizBaseRecord.getGrade());
            if (Objects.nonNull(item.getId())) {
                addMatter.add(item.getId());
            }
        });
        bizDiseaseDetailList.stream().forEach(item -> {
            item.setRecordId(bizBaseRecord.getId());
            item.setClassId(bizBaseRecord.getClassId());
            item.setGrade(bizBaseRecord.getGrade());
            if (Objects.nonNull(item.getId())) {
                addDisease.add(item.getId());
            }
        });
        if (Objects.nonNull(bizBaseRecord.getId())) {
            List<BizMatterDetail> matterDetailList = bizMatterDetailService.list(new LambdaQueryWrapper<BizMatterDetail>().eq(BizMatterDetail::getRecordId, bizBaseRecord.getId()));
            List<BizDiseaseDetail> diseaseDetailList = bizDiseaseDetailService.list(new LambdaQueryWrapper<BizDiseaseDetail>().eq(BizDiseaseDetail::getRecordId, bizBaseRecord.getId()));
            Set<Long> allMatter = matterDetailList.stream().map(BizMatterDetail::getId).collect(Collectors.toSet());
            Set<Long> allDisease = diseaseDetailList.stream().map(BizDiseaseDetail::getId).collect(Collectors.toSet());
            result = bizMatterDetailService.removeBatchByIds(CollUtil.subtract(allMatter, addMatter));
            result = bizDiseaseDetailService.removeBatchByIds(CollUtil.subtract(allDisease, addDisease));
        }
        result = bizMatterDetailService.saveOrUpdateBatch(bizMatterDetailList);
        result = bizDiseaseDetailService.saveOrUpdateBatch(bizDiseaseDetailList);

        return result;
    }

    @Override
    public List<BizBaseRecord> selectRecordByDateTime(String dateTime) {
        QueryWrapper<BizBaseRecord> wrapper = new QueryWrapper<>();
        if (StrUtil.isNotBlank(dateTime)) {
            wrapper.eq("create_time", dateTime);
        }
        if (LocalUserUtils.getScope() == 2) {
            wrapper.eq("create_by", LocalUserUtils.getUser().getName());
        } else if (LocalUserUtils.getScope() == 1) {
            wrapper.eq("grade", LocalUserUtils.getUser().getGrade());
        }
        wrapper.orderByDesc("create_time").orderByDesc("grade").orderByAsc("class_id");
        return bizBaseRecordMapper.selectList(wrapper);
    }

    @Override
    public PageResult<Map<String, Object>> list(String dateTime, Integer page, Integer size) {
        QueryWrapper<BizBaseRecord> wrapper = new QueryWrapper<>();
        if (StrUtil.isNotBlank(dateTime)) {
            wrapper.eq("create_time", dateTime);
        }
        if (LocalUserUtils.getScope() == 2) {
            wrapper.eq("create_by", LocalUserUtils.getUser().getName());
        } else if (LocalUserUtils.getScope() == 1) {
            wrapper.eq("grade", LocalUserUtils.getUser().getGrade());
        }
        wrapper.select("create_time").lambda().groupBy(BizBaseRecord::getCreateTime).orderByDesc(BizBaseRecord::getCreateTime);
        Page<Map<String, Object>> mapPage = bizBaseRecordMapper.selectMapsPage(new Page<>(page, size), wrapper);
        return PageUtil.toPage(mapPage);
    }

    @Override
    public void downloadRecordByDateTime(String dateTime, HttpServletResponse response) {
        List<BizBaseRecord> bizBaseRecords = selectRecordByDateTime(dateTime);
        List<RecordDto> recordDtoList = new ArrayList<>(bizBaseRecords.size());
        for (BizBaseRecord bizBaseRecord : bizBaseRecords) {
            RecordDto recordDto = new RecordDto();
            List<BizMatterDetail> matterDetailList = bizMatterDetailService.selectMatterDetailList(bizBaseRecord.getId());
            List<BizDiseaseDetail> bizDiseaseDetails = bizDiseaseDetailService.selectDiseaseDetailList(bizBaseRecord.getId());
            recordDto.setBizBaseRecord(bizBaseRecord);
            recordDto.setBizMatterDetailList(matterDetailList);
            recordDto.setBizDiseaseDetailList(bizDiseaseDetails);
            recordDtoList.add(recordDto);
        }
        try {
            download(dateTime, generateTableName(dateTime), recordDtoList, response);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public BizBaseRecord selectRecordLatest() {
        QueryWrapper<BizBaseRecord> wrapper = new QueryWrapper<>();
        wrapper.eq("create_by", LocalUserUtils.getUser().getName());
        wrapper.orderByDesc("id");
        wrapper.last("limit 1");
        return bizBaseRecordMapper.selectOne(wrapper);
    }

    private String generateTableName(String dateTime) {
        return dateTime + "学生到校情况汇总表";
    }

    private void download(String dateTime, String tableName, List<RecordDto> recordDtoList, HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建1个Sheet（后两个为空）
        HSSFSheet sheet = workbook.createSheet("Sheet");
        createSheetContent(dateTime, workbook, sheet, recordDtoList);
        sheet.getMergedRegions().forEach(cra -> PoiExcelUtil.setRangeCellStyle(cra, sheet));

        // 设置响应头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        String encodedTableName = URLEncoder.encode(tableName, "UTF-8");
        String filename = encodedTableName + ".xlsx";
        response.setHeader("Content-Disposition", "attachment; filename=" + filename);
        // 将Excel写入响应输出流
        workbook.write(response.getOutputStream());
        // 关闭工作簿
        workbook.close();
        // 写入文件
//        try (FileOutputStream out = new FileOutputStream("test.xls")) {
//            workbook.write(out);
//        }
    }

    private void createSheetContent(String dateTime, HSSFWorkbook workbook, HSSFSheet sheet, List<RecordDto> recordDtoList) {
        titleRow0(workbook, sheet);
        titleRow1(dateTime, workbook, sheet);
        Map<Integer, List<RecordDto>> collect = recordDtoList.stream().collect(Collectors.groupingBy(recordDto -> recordDto.getBizBaseRecord().getGrade(), LinkedHashMap::new, Collectors.toList()));
        int index = contentRow2To6(workbook, sheet, collect);
        for (Map.Entry<Integer, List<RecordDto>> entry : collect.entrySet()) {
            List<BizBaseRecord> list = entry.getValue().stream().map(RecordDto::getBizBaseRecord).toList();
            index = addGradeTitle(dateTime, entry.getKey(), index, workbook, sheet, list);
            index = addGradeDetails(index + 1, workbook, sheet, entry.getValue());
        }

    }

    private int addGradeDetails(int startRow, HSSFWorkbook workbook, HSSFSheet sheet, List<RecordDto> list) {
        for (RecordDto recordDto : list) {
            BizBaseRecord bizBaseRecord = recordDto.getBizBaseRecord();
            if (bizBaseRecord.getTotalNum().intValue() == bizBaseRecord.getArrivedNum().intValue()) {
                Row dataRow = sheet.createRow(startRow++);
                extractedCommon(workbook, dataRow, bizBaseRecord);
                extracted(workbook, dataRow, bizBaseRecord);
                for (int i = 3; i < 19; i++) {
                    PoiExcelUtil.createCell(dataRow, i, "", PoiExcelUtil.createDataStyle(workbook));
                }
            } else {
                if (bizBaseRecord.getTotalNum() - bizBaseRecord.getArrivedNum() == 1) {
                    Row dataRow = sheet.createRow(startRow++);
                    extractedCommon(workbook, dataRow, bizBaseRecord);
                    if (bizBaseRecord.getMatterNum() == 1) {
                        BizMatterDetail bizMatterDetail = recordDto.getBizMatterDetailList().get(0);
                        extractedMatterInfo(workbook, dataRow, bizMatterDetail);
                        extractedMatterDateTime(workbook, dataRow, bizMatterDetail);
                    } else {
                        BizDiseaseDetail bizDiseaseDetail = recordDto.getBizDiseaseDetailList().get(0);
                        extractedDiseaseInfo(workbook, dataRow, bizDiseaseDetail);
                        extractedDiseaseDateTime(workbook, dataRow, bizDiseaseDetail);
                    }
                    extracted(workbook, dataRow, bizBaseRecord);
                } else {
                    Row firstRow = sheet.createRow(startRow++);
                    extractedCommon(workbook, firstRow, bizBaseRecord);
                    int count = 0;
                    for (int i = 0; i < recordDto.getBizMatterDetailList().size(); i++) {
                        BizMatterDetail bizMatterDetail = recordDto.getBizMatterDetailList().get(i);
                        if (i == 0) {
                            extractedMatterInfo(workbook, firstRow, bizMatterDetail);
                            extractedMatterDateTime(workbook, firstRow, bizMatterDetail);
                            extracted(workbook, firstRow, bizBaseRecord);
                            count++;
                        } else {
                            Row dataRow = sheet.createRow(startRow++);
                            for (int j = 0; j < 3; j++) {
                                PoiExcelUtil.createCell(dataRow, j, "", PoiExcelUtil.createDataStyle(workbook));
                            }
                            extractedMatterInfo(workbook, dataRow, bizMatterDetail);
                            extractedMatterDateTime(workbook, dataRow, bizMatterDetail);
                            extracted(workbook, dataRow, bizBaseRecord);
                            count++;
                        }
                    }
                    for (int i = 0; i < recordDto.getBizDiseaseDetailList().size(); i++) {
                        BizDiseaseDetail bizDiseaseDetail = recordDto.getBizDiseaseDetailList().get(i);
                        if (count == 0) {
                            extractedDiseaseInfo(workbook, firstRow, bizDiseaseDetail);
                            extractedDiseaseDateTime(workbook, firstRow, bizDiseaseDetail);
                            extracted(workbook, firstRow, bizBaseRecord);
                            count++;
                        } else {
                            Row dataRow = sheet.createRow(startRow++);
                            for (int j = 0; j < 3; j++) {
                                PoiExcelUtil.createCell(dataRow, j, "", PoiExcelUtil.createDataStyle(workbook));
                            }
                            extractedDiseaseInfo(workbook, dataRow, bizDiseaseDetail);
                            extractedDiseaseDateTime(workbook, dataRow, bizDiseaseDetail);
                            extracted(workbook, dataRow, bizBaseRecord);
                        }
                    }
                }
            }
        }
        return startRow;
    }

    private void extractedMatterDateTime(HSSFWorkbook workbook, Row dataRow, BizMatterDetail bizMatterDetail) {
        PoiExcelUtil.createCell(dataRow, 16, Objects.nonNull(bizMatterDetail.getLeaveDate()) ? bizMatterDetail.getLeaveDate().format(dateTimeFormatter) : "", PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 17, bizMatterDetail.getLeaveDuration(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 18, Objects.nonNull(bizMatterDetail.getBackDate()) ? bizMatterDetail.getBackDate().format(dateTimeFormatter) : "", PoiExcelUtil.createDataStyle(workbook));
    }

    private void extractedDiseaseDateTime(HSSFWorkbook workbook, Row dataRow, BizDiseaseDetail bizDiseaseDetail) {
        PoiExcelUtil.createCell(dataRow, 16, Objects.nonNull(bizDiseaseDetail.getLeaveDate()) ? bizDiseaseDetail.getLeaveDate().format(dateTimeFormatter) : "", PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 17, bizDiseaseDetail.getLeaveDuration(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 18, Objects.nonNull(bizDiseaseDetail.getBackDate()) ? bizDiseaseDetail.getBackDate().format(dateTimeFormatter) : "", PoiExcelUtil.createDataStyle(workbook));
    }

    private void extractedMatterInfo(HSSFWorkbook workbook, Row dataRow, BizMatterDetail bizMatterDetail) {
        PoiExcelUtil.createCell(dataRow, 3, bizMatterDetail.getName(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 4, bizMatterDetail.getGender() == 0 ? "男" : "女", PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 5, bizMatterDetail.getReason(), PoiExcelUtil.createDataStyle(workbook));
        for (int i = 6; i < 16; i++) {
            PoiExcelUtil.createCell(dataRow, i, "", PoiExcelUtil.createDataStyle(workbook));
        }
    }

    private void extractedDiseaseInfo(HSSFWorkbook workbook, Row dataRow, BizDiseaseDetail bizDiseaseDetail) {
        PoiExcelUtil.createCell(dataRow, 3, bizDiseaseDetail.getName(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 4, bizDiseaseDetail.getGender() == 0 ? "男" : "女", PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 5, "", PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 6, bizDiseaseDetail.getReason(), PoiExcelUtil.createDataStyle(workbook));
        if (bizDiseaseDetail.getDiagnosed() == 0) {
            PoiExcelUtil.createCell(dataRow, 7, "否", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 8, "", PoiExcelUtil.createDataStyle(workbook));
        } else if (bizDiseaseDetail.getDiagnosed() == 1) {
            PoiExcelUtil.createCell(dataRow, 7, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 8, "是", PoiExcelUtil.createDataStyle(workbook));
        }
        PoiExcelUtil.createCell(dataRow, 9, bizDiseaseDetail.getReason(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 10, bizDiseaseDetail.getReason(), PoiExcelUtil.createDataStyle(workbook));
        if (bizDiseaseDetail.getEpidemic() == 0) {
            PoiExcelUtil.createCell(dataRow, 11, "否", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 12, "", PoiExcelUtil.createDataStyle(workbook));
        } else if (bizDiseaseDetail.getEpidemic() == 1) {
            PoiExcelUtil.createCell(dataRow, 11, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 12, "是", PoiExcelUtil.createDataStyle(workbook));
        }
        if (bizDiseaseDetail.getHandleWay() == 0) {
            PoiExcelUtil.createCell(dataRow, 13, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 14, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 15, "是", PoiExcelUtil.createDataStyle(workbook));
        } else if (bizDiseaseDetail.getEpidemic() == 1) {
            PoiExcelUtil.createCell(dataRow, 13, "是", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 14, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 15, "", PoiExcelUtil.createDataStyle(workbook));
        } else if (bizDiseaseDetail.getEpidemic() == 2) {
            PoiExcelUtil.createCell(dataRow, 13, "", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 14, "是", PoiExcelUtil.createDataStyle(workbook));
            PoiExcelUtil.createCell(dataRow, 15, "", PoiExcelUtil.createDataStyle(workbook));
        }
    }

    private void extractedCommon(HSSFWorkbook workbook, Row dataRow, BizBaseRecord bizBaseRecord) {
        PoiExcelUtil.createCell(dataRow, 0, bizBaseRecord.getGrade() + "." + bizBaseRecord.getClassId(), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 1, String.valueOf(bizBaseRecord.getTotalNum()), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 2, String.valueOf(bizBaseRecord.getArrivedNum()), PoiExcelUtil.createDataStyle(workbook));
    }

    private void extracted(HSSFWorkbook workbook, Row dataRow, BizBaseRecord bizBaseRecord) {
        PoiExcelUtil.createCell(dataRow, 19, String.valueOf(bizBaseRecord.getCreateBy()), PoiExcelUtil.createDataStyle(workbook));
        PoiExcelUtil.createCell(dataRow, 20, String.valueOf(bizBaseRecord.getDescription()), PoiExcelUtil.createDataStyle(workbook));
    }

    private int addGradeTitle(String dateTime, Integer grade, int startRow, HSSFWorkbook workbook, HSSFSheet sheet, List<BizBaseRecord> list) {
        // 合并年级标题
        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 20));
        Row gradeRow = sheet.createRow(startRow++);
        String gradeStr = "";
        if (grade == 1) {
            gradeStr = "一";
        } else if (grade == 2) {
            gradeStr = "二";
        } else if (grade == 3) {
            gradeStr = "三";
        }
        PoiExcelUtil.createCell(gradeRow, 0, "高陵三中高（" + gradeStr + "）缺勤原因追踪登记表  日期：" + dateTime, PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));

        int gradeAllTotalNum = list.stream().mapToInt(BizBaseRecord::getTotalNum).sum();
        int gradeAllArrivedNum = list.stream().mapToInt(BizBaseRecord::getArrivedNum).sum();
        int gradeAllMatterNum = list.stream().mapToInt(BizBaseRecord::getMatterNum).sum();
        int gradeAllDiseaseNum = list.stream().mapToInt(BizBaseRecord::getDiseaseNum).sum();

        // 统计行
        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 5));
        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 16, 20));
        Row statRow = sheet.createRow(startRow++);
        PoiExcelUtil.createCell(statRow, 6, "应到：", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 7, String.valueOf(gradeAllTotalNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 8, "实到：", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 9, String.valueOf(gradeAllArrivedNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 10, "缺勤：", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 11, String.valueOf(gradeAllTotalNum - gradeAllArrivedNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 12, "事假：", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 13, String.valueOf(gradeAllMatterNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 14, "病假：", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(statRow, 15, String.valueOf(gradeAllDiseaseNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));

        int titleNum1 = startRow;
        int titleNum2 = titleNum1 + 1;
        int titleNum3 = titleNum2 + 1;
        int titleNum4 = titleNum3 + 1;
        Row titleRow1 = sheet.createRow(titleNum1);
        Row titleRow2 = sheet.createRow(titleNum2);
        Row titleRow3 = sheet.createRow(titleNum3);
        Row titleRow4 = sheet.createRow(titleNum4);

        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 16, 16));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 17, 17));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 18, 18));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 19, 19));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum4, 20, 20));
        sheet.addMergedRegion(new CellRangeAddress(titleNum1, titleNum1, 3, 15));
        sheet.addMergedRegion(new CellRangeAddress(titleNum2, titleNum4, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(titleNum2, titleNum4, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(titleNum2, titleNum2, 6, 15));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum3, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum3, 11, 12));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum3, 13, 15));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum4, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum4, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum4, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(titleNum3, titleNum4, 10, 10));

        PoiExcelUtil.createCell(titleRow1, 0, "班级", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 1, "应到人数", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 2, "实到人数", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 3, "缺勤原因信息追踪情况", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 16, "请假日期", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 17, "请假时长", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 18, "返校时间", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 19, "班主任签名", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow1, 20, "备注", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow2, 3, "姓名", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow2, 4, "性别", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow2, 5, "事假追踪", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow2, 6, "病假追踪", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 5, "事由", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 6, "主要症状", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 7, "是否就诊", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 9, "诊断结果", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 10, "就诊医院", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 11, "传染病", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow3, 13, "处理", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 7, "是", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 8, "否", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 11, "是", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 12, "否", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 13, "门诊", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 14, "住院", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(titleRow4, 15, "居家治疗", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));

        return titleNum4;
    }

    private int contentRow2To6(HSSFWorkbook workbook, HSSFSheet sheet, Map<Integer, List<RecordDto>> collect) {
        // 表头
        Row headerRow = sheet.createRow(2);
        String[] headers = {"年级", "总人数", "实到人数", "事假人数", "病假人数"};
        for (int i = 0; i < headers.length; i++) {
            PoiExcelUtil.createCell(headerRow, i, headers[i], PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        }
        int allTotalNum = 0;
        int allArrivedNum = 0;
        int allMatterNum = 0;
        int allDiseaseNum = 0;
        int rowNum = 3;
        for (Map.Entry<Integer, List<RecordDto>> entry : collect.entrySet()) {
            Row row = sheet.createRow(rowNum++);
            List<BizBaseRecord> list = entry.getValue().stream().map(RecordDto::getBizBaseRecord).toList();
            int gradeAllTotalNum = list.stream().mapToInt(BizBaseRecord::getTotalNum).sum();
            int gradeAllArrivedNum = list.stream().mapToInt(BizBaseRecord::getArrivedNum).sum();
            int gradeAllMatterNum = list.stream().mapToInt(BizBaseRecord::getMatterNum).sum();
            int gradeAllDiseaseNum = list.stream().mapToInt(BizBaseRecord::getDiseaseNum).sum();
            if (entry.getKey() == 1) {
                PoiExcelUtil.createCell(row, 0, "高一", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            } else if (entry.getKey() == 2) {
                PoiExcelUtil.createCell(row, 0, "高二", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            } else if (entry.getKey() == 3) {
                PoiExcelUtil.createCell(row, 0, "高三", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            }
            PoiExcelUtil.createCell(row, 1, String.valueOf(gradeAllTotalNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            PoiExcelUtil.createCell(row, 2, String.valueOf(gradeAllArrivedNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            PoiExcelUtil.createCell(row, 3, String.valueOf(gradeAllMatterNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            PoiExcelUtil.createCell(row, 4, String.valueOf(gradeAllDiseaseNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
            allTotalNum = allTotalNum + gradeAllTotalNum;
            allArrivedNum = allArrivedNum + gradeAllArrivedNum;
            allMatterNum = allMatterNum + gradeAllMatterNum;
            allDiseaseNum = allDiseaseNum + gradeAllDiseaseNum;
        }
        Row sumRow = sheet.createRow(rowNum++);
        PoiExcelUtil.createCell(sumRow, 0, "合计", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(sumRow, 1, String.valueOf(allTotalNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(sumRow, 2, String.valueOf(allArrivedNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(sumRow, 3, String.valueOf(allMatterNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        PoiExcelUtil.createCell(sumRow, 4, String.valueOf(allDiseaseNum), PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
        return rowNum;
    }

    private void titleRow1(String dateTime, HSSFWorkbook workbook, HSSFSheet sheet) {
        // 统计日期
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
        Row dateRow = sheet.createRow(1);
        PoiExcelUtil.createCell(dateRow, 0, "统计日期:", PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.RIGHT));
        PoiExcelUtil.createCell(dateRow, 4, dateTime, PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
    }

    private void titleRow0(HSSFWorkbook workbook, HSSFSheet sheet) {
        // 合并标题行
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        Row titleRow = sheet.createRow(0);
        String title = generateTitle();
        PoiExcelUtil.createCell(titleRow, 0, title, PoiExcelUtil.createHeaderStyle(workbook, HorizontalAlignment.CENTER));
    }

    private String generateTitle() {
        LocalDate now = LocalDate.now();
        if (now.getMonth().getValue() >= 8) {
            return now.getYear() + "---" + now.getYear() + 1 + "学年度第一学期学生到校情况汇总表";
        } else {
            return now.getYear() - 1 + "---" + now.getYear() + "学年度第二学期学生到校情况汇总表";
        }
    }

}
